package com.sinosoft.cnmuseum.utils;

import java.io.DataOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import com.sinosoft.commontools.record.Record;



public class testTB {
	
	public static void main(String[] args) {
		new testTB().getorg();
	}
	public final static String dbDriver = "com.mysql.jdbc.Driver";
	//public final static String dbDriver = "com.p6spy.engine.spy.P6SpyDriver";
	public final static String dbURL = "jdbc:mysql://localhost:3306/dpm_da?useUnicode=true&amp;characterEncoding=UTF-8";
	public final static String dbUser = "root";
	public final static String dbPass = "root";

	

	public void getorg(){
		try{
			/*id
			actualposition
			actualpositionid
			email
			gender
			genderid
			mobilephone
			office
			orgid
			orgname
			personid
			position
			positionid
			sortsno
			status
			userid
			username
			workstatus
			workstatusid */
			Class.forName(dbDriver);
			Connection conn = DriverManager.getConnection(dbURL, dbUser, dbPass);
			String sql ="INSERT INTO `dpm_da`.`base_org`(`canceltime`, "
					+ "`iconCls`, `isvirtual`, `levelid`, `nickname`, "
					+ "`orgid`, `orglevel`, `orgname`, `parentorgid`, `sortsno`, `status`) VALUES "
					+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

			//String sql = "select * from information_schema.columns where table_schema ='dpm_da'  and table_name = 'base_user'";
			//Statement stat = conn.createStatement();
			//ResultSet rs = stat.executeQuery(sql);
			PreparedStatement pst=conn.prepareStatement(sql);
			
			String url = "http://umcbak.dpm.org.cn:8080/gguser/org.getAllOrgs.do";
			String s = CharSetUtil.decodeUnicode(testTB.get(url));
			
			Record userJSON = new Record(s);
			List<Record> userJSONJSONdatas = (List<Record>)userJSON.getValue("data");
			for (int i = 0; i < userJSONJSONdatas.size(); i++) {
				try {
					Record userJSONobj = userJSONJSONdatas.get(i);
					pst.setString(1, userJSONobj.getStrValue("canceltime"));
					pst.setString(2, userJSONobj.getStrValue("iconCls"));
					pst.setString(3, userJSONobj.getStrValue("isvirtual"));
					pst.setInt(4, userJSONobj.getIntValue("levelid"));
					pst.setString(5, userJSONobj.getStrValue("nickname"));
					pst.setString(6, userJSONobj.getStrValue("orgid"));
					pst.setString(7, userJSONobj.getStrValue("orglevel"));
					pst.setString(8, userJSONobj.getStrValue("orgname"));
					pst.setString(9, userJSONobj.getStrValue("parentorgid"));
					pst.setString(10, userJSONobj.getStrValue("sortsno"));
					pst.setString(11, userJSONobj.getStrValue("status"));
					pst.execute(); 
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			pst.close();conn.close();
			System.out.println(1);
		}catch(Throwable tr){
			
		}
		
	}

	public void getuser(){
		try{
			/*id
			actualposition
			actualpositionid
			email
			gender
			genderid
			mobilephone
			office
			orgid
			orgname
			personid
			position
			positionid
			sortsno
			status
			userid
			username
			workstatus
			workstatusid */
			Class.forName(dbDriver);
			Connection conn = DriverManager.getConnection(dbURL, dbUser, dbPass);
			String sql ="INSERT INTO `dpm_da`.`base_user`(`actualposition`, "
					+ "`actualpositionid`, "
			+ "`email`, `gender`, `genderid`, `mobilephone`, `office`, `orgid`, `orgname`, "
			+ "`personid`, `position`, `positionid`, `sortsno`, `status`, `userid`, "
			+ "`username`, `workstatus`, `workstatusid`) VALUES (?, ?, ?, "
			+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?)";

			//String sql = "select * from information_schema.columns where table_schema ='dpm_da'  and table_name = 'base_user'";
			//Statement stat = conn.createStatement();
			//ResultSet rs = stat.executeQuery(sql);
			PreparedStatement pst=conn.prepareStatement(sql);
			
			String url = "http://umcbak.dpm.org.cn:8080/gguser/user.getAllUsers.do";
			String s = CharSetUtil.decodeUnicode(testTB.get(url));
			
			Record userJSON = new Record(s);
			List<Record> userJSONJSONdatas = (List<Record>)userJSON.getValue("data");
			for (int i = 0; i < userJSONJSONdatas.size(); i++) {
				try {
					Record userJSONobj = userJSONJSONdatas.get(i);
					pst.setString(1, userJSONobj.getStrValue("actualposition"));
					pst.setInt(2, userJSONobj.getIntValue("actualpositionid"));
					pst.setString(3, userJSONobj.getStrValue("email"));
					pst.setString(4, userJSONobj.getStrValue("gender"));
					pst.setInt(5, userJSONobj.getIntValue("genderid"));
					pst.setString(6, userJSONobj.getStrValue("mobilephone"));
					pst.setString(7, userJSONobj.getStrValue("orgname"));
					pst.setString(8, userJSONobj.getStrValue("orgid"));
					pst.setString(9, userJSONobj.getStrValue("office"));
					pst.setString(10, userJSONobj.getStrValue("personid"));
					pst.setString(11, userJSONobj.getStrValue("position"));
					pst.setString(12, userJSONobj.getStrValue("positionid"));
					pst.setString(13, userJSONobj.getStrValue("sortsno"));
					pst.setInt(14, userJSONobj.getIntValue("status"));
					pst.setString(15, userJSONobj.getStrValue("userid"));
					pst.setString(16, userJSONobj.getStrValue("username"));
					pst.setString(17, userJSONobj.getStrValue("workstatus"));
					pst.setInt(18, userJSONobj.getIntValue("workstatusid"));
					pst.execute(); 
				} catch (Exception e) {
					e.printStackTrace();
				}

			}
			pst.close();conn.close();
			System.out.println(1);
		}catch(Throwable tr){
			
		}
		
	}

	  private static final String CHARSET = "UTF-8";
	  private static final int DEFAULT_CONNECTION_TIMEOUT = (20 * 1000); // 20seconds
	  private static final int DEFAULT_SOCKET_TIMEOUT = (30 * 1000); // 30seconds
	
	public static String get(final String url) {
	    HttpURLConnection conn = null;
	    DataOutputStream outStream = null;
	    try {
	      String newUrl = url;
	      URL realUrl = new URL(newUrl);
	      conn = (HttpURLConnection) realUrl.openConnection();
	      conn.setConnectTimeout(DEFAULT_CONNECTION_TIMEOUT);
	      conn.setReadTimeout(DEFAULT_SOCKET_TIMEOUT);
	      conn.setUseCaches(false);
	      conn.setDoOutput(true);
	      conn.setRequestProperty("Connection", "Keep-Alive");
	      conn.setRequestProperty("Charset", CHARSET);
	      conn.setRequestProperty("user-agent",
	          "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
	      conn.connect();

	      if (conn.getResponseCode() == 200) {
	        InputStream in = conn.getInputStream();
	        StringBuffer sb = new StringBuffer();
	        InputStreamReader reader = new InputStreamReader(in, CHARSET);
	        char[] buff = new char[1024];
	        int len;
	        while ((len = reader.read(buff)) > 0) {
	          sb.append(buff, 0, len);
	        }
	        return sb.toString();
	      } else {
	        System.out.println(conn.getResponseCode());
	        throw new Exception("ResponseCode=" + conn.getResponseCode());
	      }

	    } catch (IOException ex) {

	    } catch (Exception e) {
	    } finally {
	      if (null != outStream) {
	        try {
	          outStream.close();
	        } catch (IOException e) {
	        }
	      }
	      if (null != conn) {
	        conn.disconnect();
	      }
	    }

	    return null;
	  }
	
}
